#!/usr/bin/php
<?PHP

class SQL
{
    const ALL = 2;
    const ROW = 1;
    const COL = 0;

    private $db;
    private $table;

    public function __construct($file, $table = null)
    {
        $this->db = new SQLite3($file);
        if (!$this->db)
            return FALSE;

        $this->table = $table;

        return TRUE;
    }

    public function __set($name, $value)
    {
        $this->$name = $value;
    }

    public function __get($name)
    {
        return $this->$name;
    }

    static public function exists($db, $table, $field = null, $value = null)
    {
        if ($field === null) {
            $sql = "select name from sqlite_master where type = 'table'";
            $sql .= " and name ='$table'";
        } else {
            $sql = sprintf("select %s from %s where %s='%s'", $field, $table, $field, $value);
        }

        if ($db->querySingle($sql) === NULL)
            return FALSE;

        return TRUE;
    }

    public function istable($table)
    {
        if ($table === null)
            $table = $this->table;

        return $table;
    }

    public function create($fileds = array(), $table = null)
    {
        if (($table = $this->istable($table)) === null
                || SQL::exists($this->db, $table))
            return FALSE;

        $sql = 'CREATE TABLE '.$table;
        $sql .= '('.implode(",", $fileds).');';

        echo $sql, PHP_EOL;

        return $this->db->exec($sql);
    }

    public function add($fields = array(), $table = null)
    {
        if (($table = $this->istable($table)) === null ||
                ($count = count($fields)) == 0)
            return FALSE;

        $sql = 'INSERT INTO '.$table;
        $sql .= ' ('.implode(',', array_keys($fields)).') ';
        $sql .= 'VALUES';
        if ($count == 1)
            $sql .= " ('".array_pop($fields)."')";
        else
            $sql .= " ('".implode("','", $fields)."')";

        echo $sql, PHP_EOL;

       if ($this->db->exec($sql))
           return $this->db->lastInsertRowID();

        return FALSE;
    }

    public function edit($fields = array(), $where = "", $table = null)
    {
        if (($table = $this->istable($table)) === null
                || count($fields) == 0)
            return FALSE;

        $sql = 'UPDATE '.$table. ' SET ';
        foreach ($fields as $key => $val) {
            $sql .= $key."='".$val."',";
        }
        $sql = rtrim($sql, ',').' where '.$where;

        echo $sql, PHP_EOL;

       return $this->db->exec($sql);
    }

    public function del($where, $table = null)
    {
        if (($table = $this->istable($table)) === null)
            return FALSE;

       $sql = 'DELETE FROM '.$table.' '.$where;

       return $this->db->exec($sql);
    }

    public function query($sql, $type = SQL::ALL)
    {
        if ($type == SQL::ALL) {
            $result = $this->db->query($sql);
            if ($result) {
                $data = array();
                while ($row = $result->fetchArray(SQLITE3_ASSOC))
                    $data[] = $row;
                return $data;
            }
            return FALSE;
        }
        return $this->db->querySingle($sql, $type);
    }
}


$db = new SQL("kyo.db");

$db->table = "test";
$db->create(array(
    'id integer primary key',
    'name text not null',
    'age int not null',
    'sex int not null'
));

$db->add(array('name' => 'kyo', 'age' => 14, 'sex' => 1));
$db->add(array('name' => 'tom', 'age' => 15, 'sex' => 0));
$db->add(array('name' => 'mary', 'age' => 16, 'sex' => 1));
$db->add(array('name' => 'kyo', 'age' => 24, 'sex' => 0));

$db->edit(array('name' => 'php'), 'id=1');

print_r($db->query('select * from test', SQL::ROW));

